import os
import time

import xlwt
from django.conf.global_settings import MEDIA_ROOT
from django.db.models import Q

from schoolBook import models
from shareAPI2020 import settings


def loadfile(username,schoolname):
    print(username)
    # # 指定数据类型
    # response = HttpResponse(content_type='application/ms-excel')
    # # 设置文件名称
    # response['Content-Disposition'] = 'attachment; filename="result.xls"'
    # 创建工作簿
    wb = xlwt.Workbook(encoding='utf-8')
    # 创建表
    ws = wb.add_sheet('Menu',cell_overwrite_ok=True)
    row_num = 0
    font_style = xlwt.XFStyle()
    # 二进制
    font_style.font.bold = True
    # 表头内容
    columns = ['序号', '书号（ISBN）', '书名/刊名（题名）', '图书分类（分类号）', '作者（著者）', '出版单位', '出版日期',
               '定价（单价）', '馆藏复本数', '购买时间', '通用图书','附加资料']
    # 写进表头内容
    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)
    # Sheet body, remaining rows
    font_style = xlwt.XFStyle()
    # 获取数据库数据

    rows = models.MS_BookLibrary.objects.filter(Q(BOOK_School__USERNAME=username)).values_list('isbn', 'title', 'genus', 'author', 'publisher', 'pubdate', 'price',
                                                     'page', 'page', 'page','ciptxt')
    # 遍历提取出来的内容
    print(rows)
    for row in rows:
        row_num += 1
        # 逐行写入Excel row从1开始，col_num从0开始
        for col_num in range(len(row)):
            print(col_num)
            ws.write(row_num, 0, row_num, font_style)
            ws.write(row_num, col_num + 1, row[col_num], font_style)
    filename = time.time()
    wb.save(os.path.join(settings.MEDIA_ROOT,f'{schoolname}{filename}.xls'))
    return f'www.tbzhu.com/media/{schoolname}{str(filename)}.xls'


